<?php

class Application_Model_DbTable_User extends Application_Model_DbTable_DomainObject {

    protected $_name = 'uuch_user'; //用户信息

    public function getMapper() {
        return array('id', 'email', 'nikename', 'realname', 'datecreated', 'password', 'passwordsalt'
            , 'username', 'lastLogin', 'userLevel', 'synKey', 'userType', 'pic', 'state', 'logintimes'
            , 'loginip', 'storeno', 'remark', 'paypal', 'locationtime', 'source', 'bindId', 'recommendId','cityId', 'phone');
    }

    public function getItemCount() {
        $db = $this->getAdapter();
        $result = $db->query('select count(*) from uuch_user');
        $c = $result->fetchColumn();
        return $c;
    }
    public function getAlluserid() {
        $db = $this->getAdapter();
        $result = $db->query('select id from uuch_user where email not like \'%@uuch.com\' ');
        return $result->fetchAll();
    }

    public function customerList() {
        $db = $this->getAdapter();
        $customers = $db->query('SELECT u.id, u.nikename, u.storeno, lastlogin, money, num, wayLastTime, o.sum AS orderMoney, orderCount, orderLastTime, balance 
                FROM uuch_user u 
                JOIN (SELECT userid, SUM( lastmoney ) money, COUNT( 1 ) num, MAX( datecreated ) wayLastTime FROM uuch_waybill WHERE STATUS >0 GROUP BY userid ORDER BY money DESC)m 
                ON u.id = m.userid 
                LEFT JOIN (SELECT userid, SUM( sum ) sum, COUNT( 1 ) orderCount, MAX( dateCreated ) orderLastTime FROM uuch_order WHERE STATUS >0 GROUP BY userid)o 
                ON u.id = o.userid 
                LEFT JOIN (SELECT id,money balance FROM uuch_account)a
                ON a.id = u.id  
                ORDER BY money DESC limit 0,100');
        return $customers->fetchAll();
    }

    public function getByUserId($userid,$years) {
        $db = $this->getAdapter();
        $customers = $db->query("SELECT DATE_FORMAT(datePaid,'%Y-%m') AS month,count(id) as num,SUM(lastMoney) as money FROM uuch_waybill " . 
        " where userId = " .$userid . " and year(datePaid) = ".$years." and status > 0 group by month ORDER BY month DESC");
        return $customers->fetchAll();
    }
}